package com.eascs.web.o2o.web.service;

import com.eascs.app.dao.JdbcSession;
import com.eascs.web.o2o.web.entity.LoanBalance;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

@Service
public class LoanBalanceForEchartsService {
	
	
	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	JdbcSession jdbcSessionO2OService;

	
	public List<Map<String,Object>> LoanBalanceForPlat(String cityName,String typeName){
		StringBuffer querySql = new StringBuffer("SELECT h.platform_name ");
		if ((typeName).equals("放款金额")) {
			querySql.append(" ,sum(t.loan_amount) loan_amount");
		}
		if ((typeName).equals("银行正常贷款余额")) {
			querySql.append(" ,sum(t.outstanding_of_deposits) outstanding_of_deposits");
		}
		if ((typeName).equals("银行逾期贷款余额")) {
			querySql.append(" ,sum(t.defaultcapital) defaultcapital");
		}
		if ((typeName).equals("代偿贷款金额	")) {
			querySql.append(" ,sum(t.compensation) compensation");
		}
		if ((typeName).equals("代偿贷款欠还余额")) {
			querySql.append(" ,sum(t.compensatory_balance) compensatory_balance");
		}
				
		querySql.append(" FROM o2o_balance_his t INNER JOIN o2o_platform_regional h ON t.ltd = h.platform_id  inner join pub_dditem p ON h.natural_city_code = p.item_code  AND p.is_lock='N' AND p.dd_no = 160  where 1=1  ");
		querySql.append(" AND p.item_name = "+"'"+cityName+"'");		
		querySql.append(" group by t.ltd order by t.ltd");		
		List<Object[]> list = jdbcSessionO2OService.findObjectsList(querySql.toString());
		List<Map<String,Object>> lp =new ArrayList<Map<String,Object>>();
		if(list!=null&&list.size()>0){
			for (int i = 0; i < list.size(); i++) {
				Object[]  o= list.get(i);
				Map<String,Object> map=new HashMap<>();
				map.put("name", o[0]);
				map.put("value", o[1]);
				lp.add(map);
			}
		}
		return lp;
	}
	
	
	
	public List<Map<String,Object>> LoanBalanceForCity(String provinceName,String typeName){
		StringBuffer querySql = new StringBuffer("SELECT p.item_name city ");
		if ((typeName).equals("放款金额")) {
			querySql.append(" ,sum(t.loan_amount) loan_amount");
		}
		if ((typeName).equals("银行正常贷款余额")) {
			querySql.append(" ,sum(t.outstanding_of_deposits) outstanding_of_deposits");
		}
		if ((typeName).equals("银行逾期贷款余额")) {
			querySql.append(" ,sum(t.defaultcapital) defaultcapital");
		}
		if ((typeName).equals("代偿贷款金额	")) {
			querySql.append(" ,sum(t.compensation) compensation");
		}
		if ((typeName).equals("代偿贷款欠还余额")) {
			querySql.append(" ,sum(t.compensatory_balance) compensatory_balance");
		}
				
		querySql.append(" FROM o2o_balance_his t INNER JOIN o2o_platform_regional h ON t.ltd = h.platform_id  inner join pub_dditem p ON h.natural_city_code = p.item_code  AND p.is_lock='N' AND p.dd_no = 160  inner join pub_dditem l ON h.natural_province_code = l.item_code AND l.dd_no = 159 where 1=1   ");
		querySql.append(" AND l.item_name = "+"'"+provinceName+"'");		
		querySql.append(" group by h.natural_city_code order by h.natural_city_code");		
		List<Object[]> list = jdbcSessionO2OService.findObjectsList(querySql.toString());
		List<Map<String,Object>> lp =new ArrayList<Map<String,Object>>();
		if(list!=null&&list.size()>0){
			for (int i = 0; i < list.size(); i++) {
				Object[]  o= list.get(i);
				Map<String,Object> map=new HashMap<>();
				map.put("name", o[0]);
				map.put("value", o[1]);
				lp.add(map);
			}
		}
		
		return lp;
	}
	
	
	
	public List<Object[]> LoanBalanceForProvince() {
		String sql="select l.item_name province,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance  from  (SELECT h.natural_province_code natural_province_code,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM o2o_balance_his t INNER JOIN o2o_platform_regional h ON t.ltd = h.platform_id where 1=1   group by h.natural_province_code order by h.natural_province_code) s inner join pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159";
		return jdbcSessionO2OService.findObjectsList(sql);
	}
	
	
	
	
	
	
	public LoanBalance LoanBalanceForProvinceObject() {
		String sql="select l.item_name province,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance  from  (SELECT h.natural_province_code natural_province_code,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM o2o_balance_his t INNER JOIN o2o_platform_regional h ON t.ltd = h.platform_id where 1=1  group by h.natural_province_code order by h.natural_province_code) s inner join pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159";
		List<Object[]> list = jdbcSessionO2OService.findObjectsList(sql);
		LoanBalance lb=new LoanBalance();
		if(list!=null&&list.size()>0){
			List<Map<String,Object>> fkye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> yhzcdkye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> yhyqye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> dcdkje = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> dcdkqhye = new ArrayList<Map<String,Object>>();
			for (int i = 0; i < list.size(); i++) {
				Object[] o = list.get(i);
				Map<String,Object> fkyemap=new HashMap<>();
				fkyemap.put("name", o[0]);
				fkyemap.put("value", o[1]);
				fkye.add(fkyemap);
				Map<String,Object> yhzcdkyemap=new HashMap<>();
				yhzcdkyemap.put("name", o[0]);
				yhzcdkyemap.put("value", o[2]);
				yhzcdkye.add(yhzcdkyemap);
				Map<String,Object> yhyqyemap=new HashMap<>();
				yhyqyemap.put("name", o[0]);
				yhyqyemap.put("value", o[3]);
				yhyqye.add(yhyqyemap);
				Map<String,Object> dcdkjemap=new HashMap<>();
				dcdkjemap.put("name", o[0]);
				dcdkjemap.put("value", o[4]);
				dcdkje.add(dcdkjemap);
				Map<String,Object> dcdkqhyemap=new HashMap<>();
				dcdkqhyemap.put("name", o[0]);
				dcdkqhyemap.put("value", o[5]);
				dcdkqhye.add(dcdkqhyemap);
			}
			lb.setFkje(fkye);
			lb.setYhzcdjye(yhzcdkye);
			lb.setYhyqdjye(yhyqye);
			lb.setDcdkye(dcdkje);
			lb.setDcdkqhye(dcdkqhye);
		}
		return lb;
	}
	
	public List<List<Map<String,Object>>> LoanBalanceForProvinceList() {
		String sql="select l.item_name province,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance  from  (SELECT h.natural_province_code natural_province_code,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM o2o_balance_his t INNER JOIN o2o_platform_regional h ON t.ltd = h.platform_id where 1=1   group by h.natural_province_code order by h.natural_province_code) s inner join pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159";
		List<Object[]> list = jdbcSessionO2OService.findObjectsList(sql);
		List<List<Map<String,Object>>> l=new ArrayList<>();
		if(list!=null&&list.size()>0){
			List<Map<String,Object>> fkye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> yhzcdkye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> yhyqye = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> dcdkje = new ArrayList<Map<String,Object>>();
			List<Map<String,Object>> dcdkqhye = new ArrayList<Map<String,Object>>();
			for (int i = 0; i < list.size(); i++) {
				Object[] o = list.get(i);
				Map<String,Object> fkyemap=new HashMap<>();
				fkyemap.put("name", o[0]);
				fkyemap.put("value", o[1]);
				fkye.add(fkyemap);
				Map<String,Object> yhzcdkyemap=new HashMap<>();
				yhzcdkyemap.put("name", o[0]);
				yhzcdkyemap.put("value", o[2]);
				yhzcdkye.add(yhzcdkyemap);
				Map<String,Object> yhyqyemap=new HashMap<>();
				yhyqyemap.put("name", o[0]);
				yhyqyemap.put("value", o[3]);
				yhyqye.add(yhyqyemap);
				Map<String,Object> dcdkjemap=new HashMap<>();
				dcdkjemap.put("name", o[0]);
				dcdkjemap.put("value", o[4]);
				dcdkje.add(dcdkjemap);
				Map<String,Object> dcdkqhyemap=new HashMap<>();
				dcdkqhyemap.put("name", o[0]);
				dcdkqhyemap.put("value", o[5]);
				dcdkqhye.add(dcdkqhyemap);
			}
			l.add(fkye);l.add(yhzcdkye);l.add(yhyqye);l.add(dcdkje);l.add(dcdkqhye);
		}
		return l;
	}

	
}
